﻿/*
 * Сделано в SharpDevelop.
 * Пользователь: Badanin M.V.
 * Дата: 28.06.2013
 * Время: 9:06
 * Проект: РПН Регион 2013
 * Распространяется под лицензией GPL v3
 */
using System;
using System.Collections.Generic;
using System.IO;
using MySql.Data.MySqlClient;
/*
 *  	
PredID 	int(11)
PredNum 	int(11)
PredDate 	date
StartDate 	date
EndDate 	date
LastEditRow 	timestamp
Del 	int(11)
IspUserID 	int(11)
IspUserDate 	timestamp
OtdelID 	int(11)
PredInfo 	text
PredInfo2 	text
KontID 	int(11) 	
 */
 
namespace Region
{
  public class Pred : DBObject
  {
      const String FileTemplateRTF="Template/Pred.rtf";
      const String FileTemplateDOCX="Template/Pred.docx";
      #region Поля 
      public Int32 PredID { get { return (Int32)getPole("PredID"); } set { setPole("PredID", value); } }
      public Int32 PredNum { get { return (Int32)getPole("PredNum"); } set { setPole("PredNum", value); } }    
      public Int32 Del { get { return (Int32)getPole("Del"); } set { setPole("Del", value); } }
      public Int32 IspUserID { get { return (Int32)getPole("IspUserID"); } set { setPole("IspUserID", value); } }      
      public Int32 OtdelID { get { return (Int32)getPole("OtdelID"); } set { setPole("OtdelID", value); } }      
      public Int32 KontID { get { return (Int32)getPole("KontID"); } set { setPole("KontID", value); } }      
      
      public RegionDate PredDate { get { return (RegionDate)getPole("PredDate"); } set { setPole("LastEditRow", value); } }
      public RegionDate StartDate { get { return (RegionDate)getPole("StartDate"); } set { setPole("StartDate", value); } }
      public RegionDate EndDate { get { return (RegionDate)getPole("EndDate"); } set { setPole("EndDate", value); } }

      public RegionDate LastEditRow { get { return (RegionDate)getPole("LastEditRow"); } set { setPole("PredDate", value); } }
      public RegionDate IspUserDate { get { return (RegionDate)getPole("IspUserDate"); } set { setPole("IspUserDate", value); } }
      
      public String PredInfo { get { return (String)getPole("PredInfo"); } set { setPole("PredInfo", value); } }
      public String PredInfo2 { get { return (String)getPole("PredInfo2"); } set { setPole("PredInfo2", value); } }

      public Otdel Otdel{get{return m_engine.getOtdel(OtdelID);} set{ OtdelID = value.OtdelID;}}
      public User IspUser{get{return m_engine.getUser(IspUserID);} set{ IspUserID = value.UserID;}}
      public Kont Kont{get{return m_engine.getKont(KontID);} set{ KontID = value.KontID;}}      
      #endregion
      HashSet<Int32> m_Services;
      public HashSet<Int32> Services{get{return new HashSet<Int32>(m_Services);}set{m_Services = new HashSet<Int32>(value);}}
      void AddServices(Int32 ServiceID){m_Services.Add(ServiceID);}
      void DelServices(Int32 ServiceID){m_Services.Remove(ServiceID);}
      #region Переопределённые методы DBObject
      public override bool save()
      {
        return base.save();
      }
      public override bool isEdit()
      {
        return base.isEdit();
      }
      public override bool read(MySqlConnection RegionDB=null)
      {
        if (!base.read(RegionDB)) return false;
        SQLResult r = m_engine.SQLSelect("SELECT `ServiceID` FROM `pred_service` WHERE `PredID`=@PredID",new SQLParam{{"@PredID",this.PredID}},RegionDB);
        m_Services = new HashSet<Int32>();
        for(Int32 i=0;i<r.Count;i++)
          m_Services.Add((Int32)r[i]["ServiceID"]);
        return true;
      }
      /// <summary>Не редактируемые поля. доступны только для администратор</summary>
      static private Dictionary<String, bool> Access_NoEdit = new Dictionary<String, bool>() { { "RasporID", true }, { "RasporNum", true }, { "RasporYear", true }, { "StatusVip", true }, { "RucPodpis", true }, { "NachPodpis", true } };
      /// <summary>Поля закрываемые подписью</summary>
      static private Dictionary<String, bool> Access_BasePoles = new Dictionary<String, bool>() { { "Date", true }, { "CheckDoc", true }, { "CheckGo", true }, { "CheckPlan", true }, { "CheckLicen", true }, { "Pred", true }, { "OsnovID", true }, { "OtdelID", true }, { "IspUserID", true }, { "KontID", true }, { "TP", true }, { "Start", true }, { "End", true }, { "IspInfo", true }, { "ExpertsInfo", true }, { "TargetInfo", true }, { "TaskInfo", true }, { "OsnovInfo", true }, { "MKontrInfo", true }, { "AReglamInfo", true }, { "PDocInfo", true }, { "RucComment", true }, { "NachComment", true }, { "Action_StatusVip", true }, { "Action_RucPodpis", true }, { "Action_NachPodpis", true } };
      public override bool poleCheck(String PoleName, Object Value) { return true; }
      public override bool poleAccess(String PoleName)
      {
          //Не редактируемые поля
          if (Access_NoEdit.ContainsKey(PoleName)) return false;
          // Администратору вообще многое можно           
          if (m_engine.isAccess("Admin")) return true;
          //Основные поля распоряжения
          if (Access_BasePoles.ContainsKey(PoleName))
          {
              /*//if (m_engine.isAccess("Admin")) return true;
              // Обработка доступа по подписям
              if (m_engine.isAccess("RucPodpis") && PoleName == "Action_RucPodpis") return true;
              if (m_engine.isAccess("RucPodpis") && PoleName == "RucComment") return true;
              if (RucPodpis > 0) return false;
              if (m_engine.isAccess("RucPodpis")) return true;
              if (m_engine.isAccess("NachPodpis") && PoleName == "Action_NachPodpis") return true;
              if (m_engine.isAccess("NachPodpis") && PoleName == "NachComment") return true;
              if (NachPodpis > 0) return false;
              if (m_engine.isAccess("NachPodpis")) return true;
              if (m_engine.isAccess("RasporEdit") && PoleName == "Action_StatusVip") return true;
              if (StatusVip) return false;*/
          }
          //Разрешить исполнитулю редактировать поля если они не запрещены правилами выше
          if (IspUserID == m_engine.CurrentUser.UserID) return true;
          return false;//Нет явынх править занчит надо запретить            
      }
      #endregion
      /// <summary>Проверить и получить список ошибок в распоряжении</summary><returns>Список ошибок</returns>
      public List<String> checkRaspor()
      {
          List<String> L = new List<string>();
          return L;
      }
      /// <summary>Экспорт в RTF</summary><param name="OutFile">Имя экспортируемого файла</param><returns>true - экспорт удался</returns>
      public bool exportRTF(String OutFile)
      {
          var Poles = new Dictionary<String, Object>();          
          Poles["PredNum"] = this.PredNum.ToString();
          Poles["Kont"] = this.Kont.KontName;
          Poles["PredDate"] = this.PredDate.ToShortDateString();
          Poles["StartDate"] = this.StartDate.ToShortDateString();
          Poles["EndDate"] = this.EndDate.ToShortDateString();
          Poles["PredInfo"] = this.PredInfo;
          Poles["PredInfo2"] = this.PredInfo2;
          Poles["FullFIO"] = this.IspUser.FullFIO;
          Poles["Telefon"] = this.IspUser.Telefon;
          return Utils.Export.export("rtf",File.ReadAllBytes(FileTemplateRTF), OutFile, Poles, null);
      }
      /// <summary>Экспорт в DOCX</summary><param name="OutFile">Имя экспортируемого файла</param><returns>true - экспорт удался</returns>
      public bool exportDOCX(String OutFile)
      {
          var Poles = new Dictionary<String, Object>();          
          Poles["%PredNum%"] = this.PredNum.ToString();
          Poles["%Kont%"] = this.Kont.KontName;
          Poles["%PredDate%"] = this.PredDate.ToShortDateString();
          Poles["%StartDate%"] = this.StartDate.ToShortDateString();
          Poles["%EndDate%"] = this.EndDate.ToShortDateString();
          Poles["%PredInfo%"] = this.PredInfo;
          Poles["%PredInfo2%"] = this.PredInfo2;
          Poles["%FullFIO%"] = this.IspUser.FullFIO;
          Poles["%Telefon%"] = this.IspUser.Telefon;
          return Utils.Export.export("docx",File.ReadAllBytes(FileTemplateDOCX), OutFile, Poles, null);
      }
      public Pred(Engine engine, Int32 PredID)
          : base(engine, "pred", "PredID", PredID)
      {
          addPoles(EPoleType.Int32, new String[] { "PredID", "IspUserID", "OtdelID", "KontID"});
          addPoles(EPoleType.Int32, new String[] { "PredNum", "Del"});
          addPoles(EPoleType.Date, new String[] { "PredDate", "StartDate", "EndDate"});
          addPoles(EPoleType.TimeStamp, new String[] { "LastEditRow", "IspUserDate"});
          addPoles(EPoleType.String, new String[] { "PredInfo", "PredInfo2"});
      }
      public override void showEditor()
      {
          /*RasporForm F = new RasporForm(m_engine, RasporID);
          F.ShowDialog();*/
      }
  }
  public class PredList
  {
    public struct Line
    {
      public Int32 PredID;
      public Int32 PredNum;      
      public RegionDate PredDate;
      public RegionDate StartDate;
      public RegionDate EndDate;
      public String KontName;
      public String IspFIO;
      /*DateTime LastEditRow;
      Int32 Del;
      Int32 IspUserID;
      DateTime IspUserDate;
      Int32 OtdelID;
      String PredInfo;
      String PredInfo2;
      Int32  KontID;*/
    }
    public bool ExportListXMLX(String FileName)
    {
      /*var EL = new List<Dictionary<String,Object>>();
      var Raions = m_engine.Raions;
      var KT = m_engine.KontType;
      var Osnov = m_engine.Osnov;
      var Otdels = m_engine.Otdels;
      setWhere("IgnorePage", true);
      List<Line> old_lines = new List<Line>(m_lines);
      Filter();
      var YNN_List = new Dictionary<Int32,String>(){{0,"Нет"},{1,"Да"},{-1,"[Не указан]"}};
      var NNar_List = new Dictionary<Int32,String>(){{0,"Выявлены"},{1,"Не выявлены"},{-1,"[Не указано]"}};
      foreach(Line line in m_lines)
      {
        var ExportLine = new Dictionary<String,Object>();
        var Raspor = m_engine.getRaspor(line.RasporID);
        
        ExportLine["%RasporNum%"] = Raspor.RasporNum;
        ExportLine["%Date%"] = Raspor.Date;
        ExportLine["%CheckDoc%"] = Raspor.CheckDoc?"Документарная":"";
        ExportLine["%CheckGo%"] = Raspor.CheckGo?"Выездная":"";
        ExportLine["%CheckPlan%"] = Raspor.CheckPlan?"Плановая":"Внеплановая";
        ExportLine["%CheckLicen%"] = Raspor.CheckLicen?"По лицензионному контролю":"";
        if (Raspor.KontID>0)
        {
          var Kont = m_engine.getKont(Raspor.KontID);
          ExportLine["%KontName%"] = Kont.KontName;
          ExportLine["%INN%"] = Kont.INN;
          ExportLine["%OGRN%"] = Kont.OGRN;
          ExportLine["%KontType%"] = KT[Kont.KontTypeID];
          ExportLine["%KontULAddress%"] = Kont.KontULAddress;
          ExportLine["%KontFKAddress%"] = Kont.KontFKAddress;
          ExportLine["%KontGosReg%"] = Kont.KontGosReg;
          ExportLine["%ObjectList%"] = "";
          var KontObjecs = Kont.KontObjects;
          foreach(var RasporObject in Raspor.RasporObjects)
            if (KontObjecs.ContainsKey(RasporObject.Value.KontObjectID))
          {                            
            var KontObject = KontObjecs[RasporObject.Value.KontObjectID];
            ExportLine["%ObjectList%"]+=  KontObject.KontObjectName + ", " + Engine.getAddressString(KontObject.KontObjectPostIndex,KontObject.KontObjectRegion,Raions[KontObject.KontObjectRaionID],KontObject.KontObjectGorod,KontObject.KontObjectNaspunkt,KontObject.KontObjectStreet,KontObject.KontObjectDom,KontObject.KontObjectKorpus,KontObject.KontObjectKvart)+";\n";
          }   
        }
        ExportLine["%IspInfo%"] = Raspor.IspInfo;
        ExportLine["%ExpertsInfo%"] = Raspor.ExpertsInfo;
        ExportLine["%TargetInfo%"] = Raspor.TargetInfo;
        ExportLine["%TaskInfo%"] = Raspor.TaskInfo;
        
        ExportLine["%TP%"] = Raspor.TP;
        ExportLine["%Start%"] = Raspor.Start;
        ExportLine["%End%"] = Raspor.End;
        ExportLine["%Osnov%"] = Osnov[Raspor.OsnovID];
        ExportLine["%OsnovInfo%"] = Raspor.OsnovInfo;
        ExportLine["%MKontrInfo%"] = Raspor.MKontrInfo;
        ExportLine["%AReglamInfo%"] = Raspor.AReglamInfo;
        ExportLine["%PDocInfo%"] = Raspor.PDocInfo;
        ExportLine["%Status%"] = Raspor.StatusVip?"Выполнено":"Черновик";            
        ExportLine["%IspUser%"] = m_engine.getUser(Raspor.IspUserID).FIO;
        ExportLine["%Otdel%"] = Otdels[Raspor.OtdelID].OtdelName;
        ExportLine["%NachPodpis%"] = Raspor.NachPodpis==0?"":m_engine.getUser(Raspor.NachPodpis).FIO;
        ExportLine["%RucPodpis%"] = Raspor.RucPodpis==0?"":m_engine.getUser(Raspor.RucPodpis).FIO;
        ExportLine["%Procuror%"] = Raspor.Procuror;
        //результаты
        ExportLine["%AktDate%"] = Raspor.AktDate;
        ExportLine["%FStart%"] = Raspor.FStart;
        ExportLine["%FEnd%"] = Raspor.FEnd;
        ExportLine["%PredpisDate%"] = Raspor.PredpisDate;
        ExportLine["%ControlDate%"] = Raspor.ControlDate;
        ExportLine["%PredpisFull%"] = YNN_List[Raspor.PredpisFull];
        ExportLine["%PredpisTime%"] = YNN_List[Raspor.PredpisTime];
        ExportLine["%NNar%"] = NNar_List[Raspor.NNar];
        
        ExportLine["%NumNar%"] = Raspor.NumNar;
        ExportLine["%Predstav%"] = YNN_List[Raspor.Predstav];
        ExportLine["%NStop%"] = YNN_List[Raspor.NStop];
        Dictionary<Int32,Protokol> Protokols = Raspor.Protokols;
        Int32 SumP=0, SumP2=0;
        String ListProtokol = "";
        var Article = m_engine.Article;
        foreach(var P in Protokols)
        {
          ListProtokol += "№"+P.Value.ProtokolNum + " от " + P.Value.ProtokolReg.ToShortDateString()+ " " + Article[P.Value.ArticleID]+(P.Value.SumP>0?" штраф "+P.Value.SumP.ToString():"")+";\n";
          SumP+=P.Value.SumP;
          SumP2+=P.Value.SumP2;
        }
        ExportLine["%NumProtokol%"] = Protokols.Count;
        ExportLine["%ListProtokol%"] = ListProtokol;
        ExportLine["%ProtokolSumP%"] = SumP;
        ExportLine["%ProtokolSumP2%"] = SumP2;
        ExportLine["%SLic%"] = YNN_List[Raspor.SLic];
        ExportLine["%SudZ1%"] = Raspor.SudZ1;
        ExportLine["%SudZ2%"] = Raspor.SudZ2;
        ExportLine["%Nar1%"] = YNN_List[Raspor.Nar1];
        ExportLine["%Nar2%"] = YNN_List[Raspor.Nar2];
        ExportLine["%Nar3%"] = YNN_List[Raspor.Nar3];
        ExportLine["%SudK1%"] = Raspor.SudK1;
        ExportLine["%SudK2%"] = Raspor.SudK2;
        ExportLine["%SudK3%"] = Raspor.SudK3;

        
        EL.Add(ExportLine);
      }      
      setWhere("IgnorePage", false);
      m_lines=old_lines;
      var Template = m_engine.getRegionFile("ReestrRaspor","xlsx");
      var Poles = new Dictionary<String,Object>();
      var Tables = new Dictionary<String,List<Dictionary<String,Object>>>(){{"%RasporNum%",EL}};
      Export.export("xlsx",Template,FileName,Poles,Tables);*/
      return true;
    }
    private Engine m_engine;
    private List<Line> m_lines;
    private Dictionary<String, Object> m_wheres;
    public Line getLine(Int32 l) { return this.m_lines[l]; }
    public Int32 NumLines { get { return m_lines.Count; } }
    private Int32 m_FullLines;
    public Int32 FullLines { get { return m_FullLines; } }
    //Кеш списка отделов
    Dictionary<Int32, Dictionary<Int32, String>> m_IspUserList = new Dictionary<Int32, Dictionary<Int32, String>>();
    /// <summary>
    /// Возвращает список исполнителей у которых есть хотяб одно предписание
    /// </summary>
    public Dictionary<Int32,String> IspUserList( Int32 ParentOtdelID)
    {
        if (m_IspUserList.ContainsKey(ParentOtdelID)) return m_IspUserList[ParentOtdelID];
        List<String> FIO = new List<String>();
        List<Int32> ID = new List<Int32>();
         Dictionary<Int32,String> R = new Dictionary<int,string>();
        SQLResult r = m_engine.SQLSelect(@"
SELECT pred.IspUserID, FIO 
FROM pred 
INNER JOIN users ON users.UserID = pred.IspUserID 
INNER JOIN otdel_otdel ON otdel_otdel.OtdelID=pred.OtdelID
WHERE  otdel_otdel.ParentOtdelID=@ParentOtdelID
GROUP BY pred.IspUserID", new SQLParam() { { "@ParentOtdelID", ParentOtdelID } });
        for (int i = 0; i < r.Count; i++) R[(Int32)r[i]["IspUserID"]] = (String)r[i]["FIO"];
        m_IspUserList[ParentOtdelID] = R;
        return R;
    }
    /// <summary>
    /// Возмращает минимальный и максимальный год выписки распоряжений
    /// </summary>
    /// <param name="MinYear"></param>
    /// <param name="MaxYear"></param>
    public void MinMaxYear(out Int32 MinYear, out Int32 MaxYear)
    {

        SQLResult r = m_engine.SQLSelect("SELECT MIN(PredDate) AS MinYear, MAX(PredDate) AS MaxYear FROM pred WHERE 1");
        if (r.Count == 1)
        {
          MinYear = ((DateTime)r[0]["MinYear"]).Year;
          MaxYear = ((DateTime)r[0]["MaxYear"]).Year;
        }
        else
        {
            MinYear = DateTime.Now.Year;
            MaxYear = DateTime.Now.Year;
        }
    }
      public PredList(Engine engine)
      {
          m_engine = engine;
          m_lines = new List<Line>();
          m_wheres = new Dictionary<String, Object>();
      }
      public bool clearWhere()
      {
          m_wheres = new Dictionary<String, Object>();
          return true;
      }
      public bool setWhere(String Where, Object Value)
      {
          m_wheres[Where] = Value;
          return true;
      }
      public bool Filter()
      {
          List<String> Tables = new List<String>();
          List<String> Poles = new List<String>();
          List<String> Wheres = new List<String>();
          List<String> OrderBy = new List<String>();
          SQLParam Params = new SQLParam();
          //Params["@Param"]=100;
          Poles.Add("`pred`.`PredID`");
          Poles.Add("`pred`.`PredNum`");
          Poles.Add("`pred`.`PredDate`");
          Poles.Add("`pred`.`StartDate`");
          Poles.Add("`pred`.`EndDate`");          
          Poles.Add("`kont`.`KontName`");
          Poles.Add("`isp`.`FIO` AS `IspFIO`");
          Tables.Add("`pred`");
          Tables.Add("LEFT JOIN `kont` ON `kont`.`KontID`=`pred`.`KontID`");
          Tables.Add("LEFT JOIN `users` AS `isp` ON `isp`.`UserID`=`pred`.`IspUserID`");
          Wheres.Add("1");
          OrderBy.Add("`pred`.`PredNum` DESC");


          Int32 PredID, ParentOtdelID, Year, PredNumStart, PredNumEnd, IspUserID, LineInPage, Page, CheckPlan;
          if (m_wheres.ContainsKey("Del") && (bool)m_wheres["Del"])
            Wheres.Add("`pred`.`Del`!=0");
          else
            Wheres.Add("`pred`.`Del`=0");
          String[] DatePoles = { "PredDate", "StartDate", "EndDate"};
          for (Int32 i = 0; i < DatePoles.Length; i++)
          {
              String Pole = DatePoles[i];
              if (m_wheres.ContainsKey(Pole + "Start") && m_wheres[Pole + "Start"] is DateTime)
              {                    
                  Wheres.Add("`pred`.`" + Pole + "` >= @" + Pole + "Start");
                  Params["@" + Pole + "Start"] = Engine.RegionDateToInt((DateTime)m_wheres[Pole+"Start"]);
              }
              if (m_wheres.ContainsKey(Pole + "End") && m_wheres[Pole + "End"] is DateTime)
              {
                  Wheres.Add("`pred`.`" + Pole + "` < @" + Pole + "End");
                  Params["@" + Pole + "End"] = Engine.RegionDateToInt((DateTime)m_wheres[Pole + "End"]);
              }
          }
          if (m_wheres.ContainsKey("PredID") && m_wheres["PredID"] != null && Int32.TryParse(m_wheres["PredID"].ToString(), out PredID))
          {
              Wheres.Add("`pred`.`PredID`=@PredID");
              Params["@RPredID"] = PredID;
          }
          if (m_wheres.ContainsKey("ParentOtdelID") && m_wheres["ParentOtdelID"] != null && Int32.TryParse(m_wheres["ParentOtdelID"].ToString(), out ParentOtdelID))
          {
              Tables.Add("INNER JOIN `otdel_otdel` ON `otdel_otdel`.`OtdelID`=`pred`.`OtdelID`");
              Wheres.Add("`otdel_otdel`.`ParentOtdelID`=@ParentOtdelID");
              Params["@ParentOtdelID"] = ParentOtdelID;
          }
          if (m_wheres.ContainsKey("Year") && m_wheres["Year"] != null && Int32.TryParse(m_wheres["Year"].ToString(), out Year))
          {
              Wheres.Add("`pred`.`PredDate`>=@YearStart AND `pred`.`PredDate`<@YearEnd");
              Params["@YearStart"] = new DateTime(Year,1,1);
              Params["@YearEnd"] = new DateTime(Year+1,1,1);
          }

          if (m_wheres.ContainsKey("PredNumStart") && m_wheres["PredNumStart"] != null && Int32.TryParse(m_wheres["PredNumStart"].ToString(), out PredNumStart) && PredNumStart > 0)
          {
              Wheres.Add("`pred`.`PredNum`>=@PredNumStart");
              Params["@PredNumStart"] = PredNumStart;
          }
          if (m_wheres.ContainsKey("PredNumEnd") && m_wheres["PredNumEnd"] != null && Int32.TryParse(m_wheres["PredNumEnd"].ToString(), out PredNumEnd) && PredNumEnd > 0)
          {
              Wheres.Add("`pred`.`PredNum`<=@PredNumEnd");
              Params["@PredNumEnd"] = PredNumEnd;
          }
          if (m_wheres.ContainsKey("IspUserID") && m_wheres["IspUserID"] != null && Int32.TryParse(m_wheres["IspUserID"].ToString(), out IspUserID) && IspUserID>0)
          {
              Wheres.Add("`pred`.`IspUserID`=@IspUserID");
              Params["@IspUserID"] = IspUserID;
          }

          String OrderBySQL = "";
          String LimitSQL = "";
          
          if (!(m_wheres.ContainsKey("IgnorePage") && (Boolean)m_wheres["IgnorePage"]))
            if (m_wheres.ContainsKey("LineInPage") && m_wheres["LineInPage"] != null && Int32.TryParse(m_wheres["LineInPage"].ToString(), out LineInPage) && LineInPage > 1)
          {
              if (!m_wheres.ContainsKey("Page") || m_wheres["Page"] == null || !Int32.TryParse(m_wheres["Page"].ToString(), out Page)) Page = 1;
              Int32 Start = (Page - 1) * LineInPage;
              LimitSQL = "LIMIT " + Start + ", " + LineInPage + " ";
          }

          if (OrderBy.Count > 0) OrderBySQL = "ORDER BY " + String.Join(", ", OrderBy.ToArray());
          String SQL = String.Format("SELECT SQL_CALC_FOUND_ROWS {0} FROM {1} WHERE ({2}) {3} {4}",
              String.Join(", ", Poles.ToArray()),
              String.Join(" ", Tables.ToArray()),
              String.Join(") AND (", Wheres.ToArray()),
              OrderBySQL, LimitSQL);
          SQLResult r = m_engine.SQLSelect(SQL,Params);
          SQLResult r2 = m_engine.SQLSelect("SELECT FOUND_ROWS() AS Count",new SQLParam());                        
          m_FullLines = (Int32)(Int64)r2[0]["Count"];
          m_lines.Clear();
          for (Int32 i = 0; i < r.Count; i++)            
          {
              Line l = new Line();
              l.PredID = (Int32)r[i]["PredID"];
              l.PredNum = (Int32)r[i]["PredNum"];
              l.PredDate = (RegionDate)r[i]["PredDate"];
              l.IspFIO = (String)r[i]["IspFIO"];
              try { l.KontName = Engine.RegionStringToString((String)r[i]["KontName"]); }
              catch { l.KontName = ""; }
              l.StartDate = (r[i]["StartDate"]==null)?new RegionDate():(RegionDate)r[i]["StartDate"];
              l.EndDate = (r[i]["EndDate"]==null)?new RegionDate():(RegionDate)r[i]["EndDate"];
              m_lines.Add(l);
          }
          return false;
      }
  }
  public partial class Engine
  {
      /// <summary> Список открытых распояжений </summary>
      private Dictionary<Int32, Pred> l_Pred;
      /// <summary> Получить распоряжение по его идентификатору</summary><param name="RasporID">ИД Распоряжения</param><returns>Объект распоряжения</returns>
      public Pred getPred(Int32 PredID)
      {
          if (l_Pred == null) l_Pred = new Dictionary<Int32, Pred>();
          if (l_Pred.ContainsKey(PredID)) return (Pred)l_Pred[PredID];
          Pred pred = new Pred(this, PredID);
          l_Pred[PredID] = pred;
          return pred;
      }
      /// <summary>Создать новое распоряжение</summary><param name="BaseRasporID">Идентификатор распоряжения на основе которого создаётся распоряжение</param><returns>Идентификатор нового расоряжения</returns>
      public Int32 newPred(Int32 BasePredID)
      {
          SQLParam Param = new SQLParam();
/*          Param["@RasporYear"] = DateTime.Now.Year;
          Param["@Date"] = RegionDateToInt(DateTime.Now);
          Param["@IspUserID"] = CurrentUser.UserID;
          Param["@OtdelID"] = CurrentUser.OtdelID;
          String[] Poles = new String[] { "CheckPlan", "CheckDoc", "CheckGo", "ExpertsInfo", "TargetInfo", "TaskInfo", "Pred", "TP", "OsnovID", "MKontrInfo", "AReglamInfo", "PDocInfo", "OsnovInfo" };
          String[] SqlPoles = new String[0];
          String SqlPolesN = "";
          //foreach (String Pole in Poles) Param["@" + Pole] = "";

          if (BaseRasporID > 0)
          {
              Raspor raspor = getRaspor(BaseRasporID);
              SqlPoles = Poles;
              foreach (String Pole in Poles)
              {
                  Param["@" + Pole] = raspor.getPole(Pole);
                  SqlPolesN += ", @" + Pole + " AS " + Pole;
              }
          }
          MySqlCommand cmd;
          SQLEdit(@"
INSERT INTO raspor(RasporNum,RasporYear,Date,IspUserID,OtdelID " + ((SqlPoles.Length > 0) ? ", " : "") + String.Join(" ,", SqlPoles) + @") 
SELECT R1.RasporNum+1 AS RasporNum, @RasporYear AS RasporYear, @Date AS Date, 
@IspUserID AS IspUserID, @OtdelID AS OtdelID " + SqlPolesN + @"
FROM raspor AS R1 
LEFT JOIN raspor AS R2 ON (R2.RasporNum=R1.RasporNum+1 AND R2.RasporYear=@RasporYear ) 
WHERE R2.RasporNum IS NULL AND R1.RasporYear=@RasporYear 
ORDER BY R1.RasporNum 
LIMIT 1", Param, out cmd);
          return (Int32)cmd.LastInsertedId;*/
          return 0;
      }
      /// <summary>Удалить распоряжение</summary><param name="RasporID">ИД распоряжения</param><returns>true - если распоряжение было удалено</returns>
      public bool delPred(Int32 PredID)
      {
          Pred pred = getPred(PredID);
          if (!CurrentUser.isAccess("EditRaspor")) return false;// Пользователь неимеет права удалять распоряжения            
          if (!CurrentUser.isAccess("Admin") && CurrentUser.OtdelID != pred.OtdelID && !Otdels[CurrentUser.OtdelID].isSubOtdel(pred.OtdelID)) return false;//нельзя удалить черновик другово отдела
          SQLParam Param = new SQLParam();
          Param["PredID"] = PredID;
          //Удалить само распоряжение
          Int32 N = SQLEdit("DELETE FROM pred WHERE PredID=@PredID", Param);
          if (N == 1) // Распоряжение действительно удалено. 
          {
              l_Pred.Remove(PredID);
              return true;
          }
          return false;
      }
  }

}
